package com.cloud.punish.api.service.impl;

import java.util.*;

import com.cloud.punish.api.entity.RecTaskNumSeq;
import com.cloud.punish.api.mapper.RecTaskNumSeqMapper;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.cloud.punish.api.bean.ScreenReqInfo;
import com.cloud.punish.api.service.RecPunishInterfaceService;
import com.cloud.punish.common.utils.DateHelper;

@Service
public class RecPunishInterfaceServiceImpl implements RecPunishInterfaceService {
	private static final Integer SELECT_HOUR = 1;
	private static final Integer SELECT_DAY = 2;
	private static final Integer SELECT_WEEK = 3;
	private static final Integer SELECT_MONTH = 4;
	private static final Integer SELECT_QUARTER = 5;
	private static final Integer SELECT_HALFYAER = 6;
	private static final Integer SELECT_YAER = 7;

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Autowired
	private RecTaskNumSeqMapper taskNumSeqMapper;

	@Override
	public List<Map<String, Object>> getRecPunishByDate(ScreenReqInfo info) {
		List<Map<String, Object>> queryForList = new ArrayList<>();
		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());
		// 01 简易 02 一般
		String unitTypeFilter = "";
		if (info.getRecType() != null && info.getRecType() == 1) {
			unitTypeFilter = " and rec_type_id = '01' ";
		} else if (info.getRecType() != null && info.getRecType() == 2) {
			unitTypeFilter = " and rec_type_id =  '02'  ";
		}
		if (info.getType().equals(SELECT_HOUR)) {
			String sql = "SELECT count(1) AS `count`,DATE_FORMAT(DATE_ADD( CONCAT(DATE_FORMAT(create_time, '%Y-%m-%d %H:'), "
					+ "(FLOOR(DATE_FORMAT(create_time, '%i') / 60))*60,':00'), INTERVAL 1 HOUR),'%H') as  timeRange "
					+ "from rec_punish " + "where create_time >= '" + start + "' " + "and create_time <= '" + end
					+ "' " + "AND unit_code LIKE '" + info.getUnitCode() + "%' " + unitTypeFilter
					+ " AND  state_id <> 99  group by  timeRange;";
			queryForList = jdbcTemplate.queryForList(sql);
		} else if (info.getType().equals(SELECT_DAY)) {
			String sql = " SELECT COUNT(1) AS `count`, DATE_FORMAT(create_time,'%m-%d') AS  timeRange FROM rec_punish where create_time >= '"
					+ start + "'  " + "and create_time <= '" + end + "' AND unit_code LIKE '" + info.getUnitCode()
					+ "%' " + unitTypeFilter + " AND  state_id <> 99 group by  timeRange ORDER BY  timeRange ASC ;";
			queryForList = jdbcTemplate.queryForList(sql);
		} else if (info.getType().equals(SELECT_WEEK)) {
			String sql = " SELECT COUNT(1) AS  	`count`, DATE_FORMAT(create_time,'%Y-%m-%d') AS  timeRange FROM rec_punish  where create_time >= '"
					+ start + "'  " + "and create_time <= '" + end + "' AND unit_code LIKE '" + info.getUnitCode()
					+ "%' " + unitTypeFilter + " AND  state_id <> 99  GROUP BY  timeRange  ORDER BY  timeRange ASC;";
			queryForList = jdbcTemplate.queryForList(sql);
		} else if (info.getType().equals(SELECT_MONTH) || info.getType().equals(SELECT_QUARTER)
				|| info.getType().equals(SELECT_HALFYAER)) {
			String sql = "SELECT COUNT(1) AS `count`, DATE_FORMAT(create_time,'%Y-%m') AS  timeRange FROM rec_punish where create_time >= '"
					+ start + "'  " + "and create_time <= '" + end + "' AND unit_code LIKE '" + info.getUnitCode()
					+ "%' " + unitTypeFilter + " AND  state_id <> 99 GROUP BY  timeRange  ORDER BY  timeRange ASC;";
			queryForList = jdbcTemplate.queryForList(sql);
		} else if (info.getType().equals(SELECT_YAER)) {
			String sql = "SELECT COUNT(1) AS `count`, DATE_FORMAT(create_time,'%Y') AS  timeRange FROM rec_punish where create_time >= '"
					+ start + "'  " + "and create_time <= '" + end + "' AND unit_code LIKE '" + info.getUnitCode()
					+ "%' " + unitTypeFilter + " AND  state_id <> 99  GROUP BY  timeRange  ORDER BY  timeRange ASC;";
			queryForList = jdbcTemplate.queryForList(sql);
		}
		// 根据类型判断返回分时、日、周 月
		return queryForList;
	}

	@Override
	public List<Map<String, Object>> getLegalCaseSource(ScreenReqInfo info) {
		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());
		// 01 简易 02 一般
		String unitTypeFilter = "";
		if (info.getRecType() != null && info.getRecType() == 1) {
			unitTypeFilter = " and rec_type_id = '01' ";
		} else if (info.getRecType() != null && info.getRecType() == 2) {
			unitTypeFilter = " and rec_type_id =  '02'  ";
		}
		String sql = " SELECT b.count,a.timeRange FROM (SELECT event_src_id, event_src_name AS timeRange FROM rec_punish WHERE  state_id <> 99  GROUP BY event_src_name)a"
				+ " left join ( SELECT count(1)AS `count`,event_src_name AS  timeRange FROM rec_punish where create_time >= '"
				+ start + "'  " + "and create_time <= '" + end + "' AND unit_code LIKE '" + info.getUnitCode() + "%' "
				+ unitTypeFilter + " AND  state_id <> 99 group by  event_src_name)b  ON a.timeRange = b.timeRange";
		List<Map<String, Object>> queryForList = jdbcTemplate.queryForList(sql);
		return queryForList;
	}

	@Override
	public List<Map<String, Object>> getLegalCaseRegion(ScreenReqInfo info) {
		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());
		// 01 简易 02 一般
		String unitTypeFilter = "";
		if (info.getRecType() != null && info.getRecType() == 1) {
			unitTypeFilter = " and rec_type_id = '01' ";
		} else if (info.getRecType() != null && info.getRecType() == 2) {
			unitTypeFilter = " and rec_type_id =  '02'  ";
		}
		String unitCodeFilter = "";
		if (!info.getUnitCode().equals("/1/")) {
			unitCodeFilter = " region_code";
		} else {
			unitCodeFilter = " LEFT (region_code, 4) AS region_code";
		}
		String doMainSql = "SELECT region_code,count(*) AS `count` FROM(SELECT   " + unitCodeFilter
				+ " FROM rec_punish where create_time >= '" + start + "'  " + "and create_time <= '" + end
				+ "' AND unit_code LIKE '" + info.getUnitCode() + "%' " + unitTypeFilter + " AND region_code != "
				+ info.getRegionCode() + " AND  state_id <> 99) t GROUP BY region_code;  ";
		return jdbcTemplate.queryForList(doMainSql);
	}

	@Override
	public List<Map<String, Object>> getLegalCaseDomain(ScreenReqInfo info) {
		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());
		// 01 简易 02 一般
		String unitTypeFilter = "";
		if (info.getRecType() != null && info.getRecType() == 1) {
			unitTypeFilter = " and rec_type_id = '01' ";
		} else if (info.getRecType() != null && info.getRecType() == 2) {
			unitTypeFilter = " and rec_type_id =  '02'  ";
		}
		String doMainSql = "SELECT count(1) AS `count`, domain_type_name AS timeRange FROM rec_punish where domain_type_name is not null and domain_type_name <> '' and create_time >= '"
				+ start + "'  " + "and create_time <= '" + end + "' AND unit_code LIKE '" + info.getUnitCode() + "%' "
				+ unitTypeFilter + "  AND  state_id <> 99 GROUP  BY domain_type_id LIMIT 0," + info.getNum();
		return jdbcTemplate.queryForList(doMainSql);
	}

	@Override
	public Map<String, Object> getLegalCaseGeneral(ScreenReqInfo info) {
		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());
		String doMainSql = "SELECT * FROM (SELECT count(*) AS punish FROM rec_punish where create_time >= '" + start
				+ "'  " + "and create_time <= '" + end + "' AND unit_code LIKE '" + info.getUnitCode() + "%' AND state_id <> 99  )a,"
				+ "(SELECT count(*) AS simple FROM rec_punish WHERE rec_type_id = '01' AND create_time >= '" + start
				+ "'  " + "and create_time <= '" + end + "' AND unit_code LIKE '" + info.getUnitCode() + "%' AND state_id <> 99)b,"
				+ "(SELECT count(*) AS normal  FROM rec_punish WHERE rec_type_id = '02' AND create_time >= '" + start
				+ "'  " + "and create_time <= '" + end + "'AND unit_code LIKE '" + info.getUnitCode() + "%' AND state_id <> 99) c ";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(doMainSql);
		Map<String, Object> map = new HashMap<>();
		if (list != null) {
			list.forEach(map::putAll);
		}
		return map;
	}

	@Override
	public List<Map<String, Object>> getLawCaseTop5(ScreenReqInfo info) {
		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());
		// 01 简易 02 一般
		String unitTypeFilter = "";
		if (info.getRecType() != null && info.getRecType() == 1) {
			unitTypeFilter = " and  rp.rec_type_id = '01' ";
		} else if (info.getRecType() != null && info.getRecType() == 2) {
			unitTypeFilter = " and rp.rec_type_id =  '02'  ";
		}
		String doMainSql = "SELECT re.law_title AS timeRange,count(1) AS `count`FROM rec_cause re LEFT JOIN rec_punish rp ON re.rec_id = rp.rec_id WHERE re.law_title IS NOT NULL "
				+ "" + unitTypeFilter + " AND rp.create_time >= '" + start + "' and rp.create_time <= '" + end
				+ "' AND rp. unit_code LIKE '" + info.getUnitCode()
				+ "%' AND  rp.state_id <> 99 GROUP BY re.law_title ORDER BY `count` DESC LIMIT 0,5";
		return jdbcTemplate.queryForList(doMainSql);
	}

	@Override
	public List<Map<String, Object>> getLawCaseTop(ScreenReqInfo info) {
		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());

		String Sql = "SELECT re.law_code AS lawcode, re.law_title AS lawtitle, count(1) AS `count` FROM rec_cause re LEFT JOIN rec_punish rp ON re.rec_id = rp.rec_id WHERE re.law_code IS NOT NULL "
				+ " AND rp.create_time >= '" + start + "' and rp.create_time <= '" + end + "' AND rp. unit_code LIKE '"
				+ info.getUnitCode() + "%' AND  rp.state_id <> 99 GROUP BY re.law_code, re.law_title ORDER BY `count` DESC";
		if (info.getNum() == null) {
			info.setNum(10);
		}
		if (info.getNum() > 0) {
			Sql += " LIMIT 0," + info.getNum();
		}
		List<Map<String, Object>> list = jdbcTemplate.queryForList(Sql);
		return list;
	}

	@Override
	public List<Map<String, Object>> getBasisCodeByRegionCode(ScreenReqInfo info) {
		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());
		String regionFilter = "LEFT (region_code, 4) AS region_code";
		if (info.getRegionid() != null && info.getRegionid() != 1) {
			regionFilter = "region_code as region_code";
		}
		String doMainSql = "SELECT " + regionFilter + ", rec.punish_basis_code, rep.unit_id FROM rec_punish rep "
				+ "LEFT JOIN rec_cause rec ON rep.rec_id = rec.rec_id WHERE rep.unit_code LIKE '" + info.getUnitCode()
				+ "%' AND rep.region_code <> '45' AND  rec.punish_basis_code IS NOT NULL AND create_time >= '" + start
				+ "'  " + "and create_time <= '" + end
				+ "'GROUP BY rec.punish_basis_code,rep.unit_id AND  rep.state_id <> 99 ORDER BY rep.region_code  DESC ";
		return jdbcTemplate.queryForList(doMainSql);
	}

	@Override
	public List<Map<String, Object>> getPerformDomain(ScreenReqInfo info) {
		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());
		String regionFilter = "LEFT (region_code, 4) AS region_code";
		if (info.getRegionid() != null && info.getRegionid() != 1) {
			regionFilter = "region_code as region_code";
		}
		String doMainSql = "SELECT " + regionFilter
				+ ", rec.punish_basis_code ,rep.domain_type_name,rep.unit_id FROM rec_punish rep "
				+ "LEFT JOIN rec_cause rec ON rep.rec_id = rec.rec_id WHERE rep.unit_code LIKE '" + info.getUnitCode()
				+ "%' AND rep.region_code <> '45' AND  rec.punish_basis_code IS NOT NULL AND rep.create_time >= '"
				+ start + "'  " + "and rep.create_time <= '" + end
				+ "'AND rep.domain_type_name <> '' AND  rep.state_id <> 99 GROUP BY rec.punish_basis_code,rep.unit_id ORDER BY rep.region_code  DESC";
		return jdbcTemplate.queryForList(doMainSql);
	}

	@Override
	public List<Map<String, Object>> getLegalCaseInfoByDate(ScreenReqInfo info) {
		List<Map<String, Object>> queryForList = new ArrayList<>();
		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());
		String idFilter = "";
		if (info.getIdList() != null && info.getIdList().size() > 0) {
			idFilter = "AND  rep.unit_id in (" + StringUtils.join(info.getIdList(), ",") + ")";
		}
		if (info.getType().equals(SELECT_DAY)) {
			String sql = " SELECT DATE_FORMAT(rep.create_time,'%m-%d') AS  timeRange ,rec.punish_basis_code,rep.unit_id "
					+ "FROM rec_punish rep LEFT JOIN rec_cause rec ON rep.rec_id = rec.rec_id where rep.create_time >= '"
					+ start + "'  " + "and rep.create_time <= '" + end + "' AND rep.unit_code LIKE '"
					+ info.getUnitCode() + "%' AND rec.punish_basis_code IS NOT NULL " + idFilter
					+ " AND  rep.state_id <> 99 GROUP BY rec.punish_basis_code,rep.unit_id  ORDER BY  timeRange ASC ";
			queryForList = jdbcTemplate.queryForList(sql);
		} else if (info.getType().equals(SELECT_WEEK)) {
			String sql = " SELECT DATE_FORMAT(rep.create_time,'%Y-%m-%d') AS  timeRange ,rec.punish_basis_code,rep.unit_id "
					+ "FROM rec_punish rep LEFT JOIN rec_cause rec ON rep.rec_id = rec.rec_id where rep.create_time >= '"
					+ start + "'  " + "and rep.create_time <= '" + end + "' AND rep.unit_code LIKE '"
					+ info.getUnitCode() + "%' AND rec.punish_basis_code IS NOT NULL " + idFilter
					+ " AND  rep.state_id <> 99 GROUP BY rec.punish_basis_code,rep.unit_id ORDER BY  timeRange ASC ";
			queryForList = jdbcTemplate.queryForList(sql);
		} else if (info.getType().equals(SELECT_MONTH)) {
			String sql = "SELECT  DATE_FORMAT(rep.create_time,'%Y-%m') AS  timeRange ,rec.punish_basis_code,rep.unit_id  "
					+ "FROM rec_punish rep LEFT JOIN rec_cause rec ON rep.rec_id = rec.rec_id where rep.create_time >= '"
					+ start + "'  " + "and rep.create_time <= '" + end + "' AND rep.unit_code LIKE '"
					+ info.getUnitCode() + "%' AND rec.punish_basis_code IS NOT NULL " + idFilter
					+ "  AND  rep.state_id <> 99 GROUP BY rec.punish_basis_code,rep.unit_id  ORDER BY  timeRange ASC ";
			queryForList = jdbcTemplate.queryForList(sql);
		}
		// 根据类型判断返回日、周 月
		return queryForList;
	}

	@Override
	public List<Map<String, Object>> getLawCaseTopForPerform(ScreenReqInfo info) {
		String idFilter = "";
		String codeFilter = "";
		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());
		if (info.getIdList() != null && info.getIdList().size() > 0) {
			idFilter = "AND  rep.unit_id in (" + StringUtils.join(info.getIdList(), ",") + ")";
		}
		if (info.getCodeList() != null && info.getCodeList().size() > 0) {
			String[] strings = new String[info.getCodeList().size()];
			info.getCodeList().toArray(strings);
			codeFilter = "AND  rec.punish_basis_code in ('" + StringUtils.join(strings, "','") + "')";
			// codeFilter = "AND rec.punish_basis_code in
			// ('"+StringUtils.join(info.getCodeList(), "',")+"')";
		}
		String sql = " SELECT count(1) AS `count`,rec.law_code AS lawcode,rec.law_title AS lawtitle "
				+ "FROM rec_punish rep LEFT JOIN rec_cause rec ON rep.rec_id = rec.rec_id where rep.create_time >= '"
				+ start + "'  " + "and rep.create_time <= '" + end + "' AND rep.unit_code LIKE '" + info.getUnitCode()
				+ "%' AND rec.punish_basis_code IS NOT NULL " + idFilter + codeFilter
				+ " AND  rep.state_id <> 99 group by  rec.law_title  ORDER BY  `count` DESC ";
		List<Map<String, Object>> queryForList = jdbcTemplate.queryForList(sql);
		return queryForList;
	}

	@Override
	public List<Map<String, Object>> getLawUseStat(ScreenReqInfo info) {

		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());

		String Sql = "SELECT re.law_code AS lawcode, re.law_title AS lawtitle, count(1) AS `count` FROM rec_cause re LEFT JOIN rec_punish rp ON re.rec_id = rp.rec_id WHERE re.law_code IS NOT NULL "
				+ " AND rp.create_time >= '" + start + "' and rp.create_time <= '" + end + "' AND rp. unit_code LIKE '"
				+ info.getUnitCode() + "%' AND  rp.state_id <> 99 GROUP BY re.law_code, re.law_title ORDER BY `count` DESC";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(Sql);
		return list;
	}

	@Override
	public List<Map<String, Object>> getCauseUseStat(ScreenReqInfo info) {
		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());

		String Sql = "SELECT re.cause_code AS lawcode, re.cause_content AS lawtitle, count(1) AS `count` FROM rec_cause re LEFT JOIN rec_punish rp ON re.rec_id = rp.rec_id WHERE re.law_code IS NOT NULL "
				+ " AND rp.create_time >= '" + start + "' and rp.create_time <= '" + end + "' AND rp. unit_code LIKE '"
				+ info.getUnitCode() + "%' AND re.law_code = '" + info.getLawID()
				+ "'AND  rp.state_id <> 99  GROUP BY re.cause_code, re.cause_content ORDER BY `count` DESC";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(Sql);
		return list;
	}

	@Override
	public Map<String, Object> getLawDynamicsInfo(String unitCode) {
		String doMainSql = "SELECT * FROM (SELECT count(*) AS simple FROM `punish`.rec_punish WHERE rec_type_id = '01'  AND unit_code LIKE '" + unitCode + "%' AND state_id <> 99 )b,"
				+ "(SELECT count(*) AS normal  FROM `punish`.rec_punish WHERE rec_type_id = '02' AND unit_code LIKE '" + unitCode + "%' AND state_id <> 99) c ";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(doMainSql);
		Map<String, Object> map = new HashMap<>();
		if (list != null) {
			list.forEach(map::putAll);
		}
		return map;
	}

	@Override
	public List<Map<String, Object>> getPunishByRegionId(ScreenReqInfo info) {
		String start = DateHelper.getFirstYearDay(info.getStart());
		String end = DateHelper.getEndDay(info.getEnd());
		String unitTypeFilter = "";
		if (info.getRecType() != null && info.getRecType() == 1) {
			unitTypeFilter = " and rec_type_id = '01' ";
		} else if (info.getRecType() != null && info.getRecType() == 2) {
			unitTypeFilter = " and rec_type_id =  '02'  ";
		}
		String sql = "SELECT UI.subject_name AS `name`,COUNT(1) AS `value` FROM punish.rec_punish rec LEFT JOIN cda.unit_info UI ON rec.unit_id = UI.unit_id WHERE UI.unit_code LIKE '"+ info.getUnitCode() + "%' AND rec.create_time >= '" + start + "' and rec.create_time <= '" + end + "'AND rec.state_id <> 99  "+unitTypeFilter+" GROUP BY UI.subject_id";
		return jdbcTemplate.queryForList(sql);
	}

	public synchronized String calculate(String recTypeId) {
		Date now = new Date();
		StringBuilder sb = new StringBuilder(DateHelper.dateToFormatStr(now, DateHelper.DATE_FORMAT_YMD));
		sb.append("01");
		sb.append(recTypeId);
		RecTaskNumSeq seq = taskNumSeqMapper.selectByPrimaryKey("01");
		int seqNum = 0;
		if (null != seq) {
			if (seq.getSeqDate().equals(DateHelper.getDateBegin(now))) {
				seqNum = seq.getSeqValue();
			}
			seq.setSeqValue(seqNum + 1);
			seq.setSeqDate(now);
			taskNumSeqMapper.updateByPrimaryKey(seq);
		} else {
			seq = new RecTaskNumSeq();
			seq.setSeqId("01");
			seq.setSeqDate(new Date());
			seq.setSeqValue(seqNum + 1);
			taskNumSeqMapper.insert(seq);
		}
		sb.append(String.format("%05d", seq.getSeqValue()));
		return sb.toString();
	}
}
